
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;

@ManagedBean
@RequestScoped
public class ContatoMB {

    private Contato contato;
    private Connection con;
    private PreparedStatement pstm;
    private List<Contato> contatos;

    public ContatoMB() throws SQLException {
        contato = new Contato();
        DriverManager.registerDriver(new org.postgresql.Driver());
        con = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5433/agenda",
                "postgres",
                "postgres");
        contatos = listaContatos();
    }

    public void setContato(Contato contato) {
        this.contato = contato;
    }

    public Contato getContato() {
        return this.contato;
    }

    public List<Contato> getContatos() {
        return this.contatos;
    }

    public void setContatos(List<Contato> contatos) {
        this.contatos = contatos;
    }    

    public void novo(){
        contato = new Contato();            
    }
       
    public void salvar() throws SQLException {
        pstm = con.prepareStatement(
                "insert into contatos(id, nome, email) "
                + "values (?, ?, ?)");
        pstm.setInt(1, contato.getId());
        pstm.setString(2, contato.getNome());
        pstm.setString(3, contato.getEmail());
        pstm.executeUpdate();
        pstm.close();
        contatos = this.listaContatos();
    }
    
    public void pesquisar() throws SQLException{
        pstm = con.prepareStatement("select * from contatos where id = ?");
        pstm.setInt(1, contato.getId());
        ResultSet rs;
        rs = pstm.executeQuery();
        if(rs.next()){
            contato.setNome(rs.getString("nome"));
            contato.setEmail(rs.getString("email"));
        }
        con.close();
    }
    
    public void alterar() throws SQLException{
        pstm = con.prepareStatement(
                "update contatos set nome = ?, email = ? where id = ?");
        pstm.setString(1, contato.getNome());
        pstm.setString(2, contato.getEmail());
        pstm.setInt(3, contato.getId());
        pstm.executeUpdate();
        pstm.close();
        contatos = this.listaContatos();
    }
    
    public void excluir() throws SQLException{
        pstm = con.prepareStatement("delete from contatos where id = ?");
        pstm.setInt(1, contato.getId());
        pstm.executeUpdate();
        pstm.close();
        this.novo();        
        contatos = this.listaContatos();
    }
    
    public List<Contato> listaContatos() throws SQLException{
        contatos = new ArrayList<Contato>();
        pstm = con.prepareStatement("select * from contatos");
        ResultSet rs;
        rs = pstm.executeQuery();
        while(rs.next()){
            contatos.add(new Contato(rs.getInt("id"), 
                    rs.getString("nome"), 
                    rs.getString("email")
            
            ));
        }
        pstm.close();   
        return contatos;        
    }
       
}
